Hive with...as...的用法

with...as...也叫做子查询部分,语句允许hive定义一个sql片段,供整个sql使用

简介

with...as...需要定义一个sql片段,会将这个片段产生的结果集保存在内存中, 后续的sql均可以访问这个结果集和,作用与视图或临时表类似.

with...as...的完整格式是这样的

-- with table_name as(子查询语句) 其他sql 
with temp as (
    select * from xxx
)
select * from temp;

只定义不实用

with temp as (
    select * from xxx
)
select * from othertable;

同级的多个temp之间用,分割with只需要一次,as后的子句必须用(),

with temp1 as (
    select * from xxx
),temp2 as (
    select * from xxx
)
select * from temp1,temp2;

with...as...当然是可以嵌套的,此处举一个简单例子

with temp2 as (
    with temp1 as (
        select * from xxx
    )
    select * from temp1
)
select * from temp2;

with...as...只能在一条sql中使用

with temp1 as (
    select * from xxx
)
select * from temp1;
select xxx from temp1; -- error! no table named temp1;